package test;

import java.sql.*;
import java.util.*;

public class DBUtil {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public DBUtil() { }
	/**
	 * 加载驱动，建立数据库连接
	 */
	public void init() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/meal?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false", "root", "root");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<Map<String,String>> getList(String sql,String[] params){
		List<Map<String,String>> list=new ArrayList<Map<String,String>>();
		init();
		try {
			pstmt = conn.prepareStatement(sql);
			// 设置sql命令中？占位符所对应的值
			setParams(params);
			//执行查询命令，返回结果集
			rs= pstmt.executeQuery();
			ResultSetMetaData rsmd= rs.getMetaData();
			while(rs.next()) {
				Map<String,String> m=new HashMap<String, String>();
				//获取列名,并将列名作为Map的键，把对应该列名的值作为Map的值
				for(int i=1;i<=rsmd.getColumnCount();i++) {
					String colName= rsmd.getColumnLabel(i);
					String v=rs.getString(colName);
					if(v!=null) m.put(colName, v);
				}
				list.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			//关闭释放所有资源
			closeAll();
		}
		return list;
	}
	public List<Map<String,String>> getList(String sql){
		return this.getList(sql, null);
	}
	/**
	 * 获取List结果集中的第一个原素（Map）
	 * @param sql
	 * @param params
	 * @return Map<String,String>
	 */
	public Map<String,String> getMap(String sql,String[] params){
		Map<String,String> m=null;
		//先获取List<Map<String,String>>集合
		List<Map<String,String>> list=this.getList(sql, params);
		if(list!=null&&list.size()!=0) 
			m=list.get(0);
		return m;
	}
	public Map<String,String> getMap(String sql){
		 
		return this.getMap(sql, null);
	}
	
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/meal", "root", "root");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public void  setParams(String[] params) {
		try {
			if (params != null) {
		 
			for (int i = 0; i < params.length; i++)
				pstmt.setString(i + 1, params[i]);
		}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void closeAll() {
		
			try {
				if(rs!=null) rs.close();
				if(pstmt!=null) pstmt.close();
				if(conn!=null) conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	public int update(String sql, String[] params) {
		int result = 0;
		init();
		try {
			pstmt = conn.prepareStatement(sql);
			// 设置sql命令中？占位符所对应的值
			setParams(params);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			//关闭释放所有资源
			closeAll();
		}

		return result;
	}
	public int update(String sql) {
		return this.update(sql,null);
	}
}